using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Xml;

namespace SqlToolkit.Oracle
{
	#region OracleDB
	/// <summary>Represent an ODP.Net Connection String and provides common/special case execution methods.</summary>
	/// <ToDo>Add persistant connection without transaction use...save resources for complex multi 
	/// method call database quries.Can be used in conjunction with transaction as well...
	/// </ToDo>
	[Serializable]
	public sealed class OracleDB : ICloneable
	{
		#region Nested Class: TransactionBundle
		/// <summary>
		/// Provides a persistant connection for ease of connection management. Also provides a Transaction ready for use.
		/// </summary>
		#endregion
		#region Nested Class: SqlAccessor
		/// <summary> Provide a higher level execution of ODP.Net routines than SqlProcedure or SqlText alone.</summary>
		[Serializable]
		public sealed class OracleDbAccessor
		{
			#region Nested Class: SqlProcedure
			/// <summary>Provides methods to access and manipulate a ODP.Net DataSource Database through Stored Procedures.</summary>
			[Serializable]
			public sealed class SqlProcedure
			{
				#region Construction
				OracleDB conn = null;
				public SqlProcedure(OracleDB connection){this.conn = connection;}
				#endregion
				#region Throw Error
				void ThorwGenProcErr(string procedure, System.Exception ex){throw new Exception("An error occuried in ODP.Net DataSource when trying to execute the following stored procedure: " + procedure + ". More detailed information has been stored in this System.Exceptions InnerException.", ex); }
				#endregion
				#region ODP.Net Helper DiscoverParameters
				/*
				/// <summary>
				/// Resolve at run time the appropriate set of System.Data.OracleClient.OracleParameters for a stored procedure
				/// </summary>
				/// <param name="connection">A valid System.Data.OracleClient.OracleConnection object</param>
				/// <param name="spName">The name of the stored procedure</param>
				/// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
				/// <returns>The parameter array discovered.</returns>
				internal static System.Data.OracleClient.OracleParameter[] DiscoverSpParameterSet(System.Data.OracleClient.OracleConnection connection, string spName, bool includeReturnValueParameter)
				{
					if( connection == null ) throw new ArgumentNullException( "connection" );
					if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
					System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(spName, connection);
					cmd.CommandType = CommandType.StoredProcedure;

					connection.Open();
					System.Data.OracleClient.OracleCommandBuilder.DeriveParameters(cmd);
					connection.Close();

					if (!includeReturnValueParameter) 
						cmd.Parameters.RemoveAt(0);

					System.Data.OracleClient.OracleParameter[] discoveredParameters = new System.Data.OracleClient.OracleParameter[cmd.Parameters.Count];
					cmd.Parameters.CopyTo(discoveredParameters, 0); 

					// Init the parameters with a DBNull value
					foreach (System.Data.OracleClient.OracleParameter discoveredParameter in discoveredParameters)
						discoveredParameter.Value = DBNull.Value;

					return discoveredParameters;
				}*/
				#endregion
				#region ExecuteReader
				public System.Data.IDataReader ExecuteReader(string procedure)
				{
					if(null == conn) return null;
					System.Data.IDataReader ret = null;
					System.Data.OracleClient.OracleCommand cmd = null;

					try
					{
						cmd = conn.CreateCommand(procedure, CommandType.StoredProcedure);

						if(null == cmd)
							return null;

						ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}

					return ret;
				}

				public System.Data.IDataReader ExecuteReader(string procedure, System.Data.OracleClient.OracleParameter param)
				{
					if(null == conn) return null;
					System.Data.IDataReader ret = null;
					System.Data.OracleClient.OracleCommand cmd = null;

					try
					{
						cmd = conn.CreateCommand(procedure, param);

						if(null == cmd)
							return null;

						ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}

					return ret;
				}
				public System.Data.IDataReader ExecuteReader(string procedure, System.Data.OracleClient.OracleParameter[] param)
				{
					if(null == conn) return null;
					System.Data.IDataReader ret = null;
					System.Data.OracleClient.OracleCommand cmd = null;

					try
					{
						cmd = conn.CreateCommand(procedure, param);

						if(null == cmd)
							return null;

						ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}

					return ret;
				}
				#endregion
				#region ExecuteXmlReader
				//public XmlReader ExecuteXmlReader(string procedure)
				//{
				//	if(null == conn) return null;
				//	XmlReader ret = null;
				//	System.Data.OracleClient.OracleCommand cmd = null;
                //
				//	try
				//	{
				//		cmd = conn.CreateCommand(procedure, CommandType.StoredProcedure);
                //
				//		if(null == cmd)
				//			return null;
                //
				//		ret = cmd.ExecuteXmlReader();
				//	}
				//	catch(Exception ex){ThorwGenProcErr(procedure, ex);}
				//	finally
				//	{
				//		cmd.Connection.Close();
				//		cmd.Dispose();
				//		cmd = null;
				//	}
                //
				//	return ret;
				//}
				//public XmlReader ExecuteXmlReader(string procedure, System.Data.OracleClient.OracleParameter param)
				//{
				//	if(null == conn) return null;
				//	XmlReader ret = null;
				//	System.Data.OracleClient.OracleCommand cmd = null;

				//	try
				//	{
				//		cmd = conn.CreateCommand(procedure, param);
                //
				//		if(null == cmd)
				//			return null;
                //
				//		ret = cmd.ExecuteXmlReader();
				//	}
				//	catch(Exception ex){ThorwGenProcErr(procedure, ex);}
				//	finally
				//	{
				//		cmd.Connection.Close();
				//		cmd.Dispose();
				//		cmd = null;
				//	}
				//	return ret;
				//}
				//public XmlReader ExecuteXmlReader(string procedure, System.Data.OracleClient.OracleParameter[] param)
				//{
				//	if(null == conn) return null;
				//	XmlReader ret = null;
				//	System.Data.OracleClient.OracleCommand cmd = null;
                //
				//	try
				//	{
				//		cmd = conn.CreateCommand(procedure, param);
                //
				//		if(null == cmd)
				//			return null;
                //
				//		ret = cmd.ExecuteXmlReader();
				//	}
				//	catch(Exception ex){ThorwGenProcErr(procedure, ex);}
				//	finally
				//	{
				//		cmd.Connection.Close();
				//		cmd.Dispose();
				//		cmd = null;
				//	}
                //
				//	return ret;
				//}
				#endregion
				#region ExecuteScalar
				public object ExecuteScalar(string procedure)
				{
					if(null == conn) return null;
					object ret = null;
					System.Data.OracleClient.OracleCommand cmd = null;

					try
					{
						cmd = conn.CreateCommand(procedure, CommandType.StoredProcedure);

						if(null == cmd)
							return null;

						ret = cmd.ExecuteScalar();
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						cmd.Connection.Close();
						cmd.Dispose();
						cmd = null;
					}

					return ret;
				}
				public object ExecuteScalar(string procedure, System.Data.OracleClient.OracleParameter param)
				{
					if(null == conn) return null;
					object ret = null;
					System.Data.OracleClient.OracleCommand cmd = null;

					try
					{
						cmd = conn.CreateCommand(procedure, param);

						if(null == cmd)
							return null;

						ret = cmd.ExecuteScalar();
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						cmd.Connection.Close();
						cmd.Dispose();
						cmd = null;
					}

					return ret;
				}
				public object ExecuteScalar(string procedure, System.Data.OracleClient.OracleParameter[] param)
				{
					if(null == conn) return null;
					object ret = null;
					System.Data.OracleClient.OracleCommand cmd = null;

					try
					{
						cmd = conn.CreateCommand(procedure, param);

						if(null == cmd)
							return null;

						ret = cmd.ExecuteScalar();
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						cmd.Connection.Close();
						cmd.Dispose();
						cmd = null;
					}

					return ret;
				}
				#endregion
				#region ExecuteNonQuery
				public int ExecuteNonQuery(string procedure)
				{
					if(null == conn) return 0;
					System.Data.OracleClient.OracleCommand cmd = null;
                    int ret = 0;
					try
					{
						cmd = conn.CreateCommand(procedure, CommandType.StoredProcedure);

						if(null == cmd)
							return 0;

						ret = cmd.ExecuteNonQuery();
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						cmd.Connection.Close();
						cmd.Dispose();
						cmd = null;
					}
                    return ret;
				}
				public int ExecuteNonQuery(string procedure, System.Data.OracleClient.OracleParameter param)
				{
					if(null == conn) return 0;
					System.Data.OracleClient.OracleCommand cmd = null;
                    int ret = 0;
					try
					{
						cmd = conn.CreateCommand(procedure, param);

						if(null == cmd)
							return 0;

						ret = cmd.ExecuteNonQuery();
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						cmd.Connection.Close();
						cmd.Dispose();
						cmd = null;
					}
                    return ret;
				}
				public int ExecuteNonQuery(string procedure, System.Data.OracleClient.OracleParameter[] param)
				{
					if(null == conn) return 0;
					System.Data.OracleClient.OracleCommand cmd = null;
                    int ret = 0;
					try
					{
						cmd = conn.CreateCommand(procedure, param);

						if(null == cmd)
							return 0;

						ret = cmd.ExecuteNonQuery();
					}
					catch(Exception ex)
					{
						ThorwGenProcErr(procedure, ex);
					}
					finally
					{
						if(null != cmd)
						{
							cmd.Connection.Close();
							cmd.Dispose();
							cmd = null;
						}
					}
                    return ret;
				}
				#endregion
				#region ExecuteDataSet
				/// <summary>
				/// Gets a DataSet Filled with the results of in passed procedure.
				/// </summary>
				/// <param name="procedure">Name of the nrocedure.</param>
				/// <returns>A DataSet or null.</returns>
				public DataSet ExecuteDataSet(string procedure)
				{
					if(null == conn) return null;

					DataSet ret = null;
					System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(procedure, CommandType.StoredProcedure);

					try
					{

						if(da == null)
							return ret;

                        ret = new DataSet();
						da.Fill(ret);
						da.SelectCommand.Connection.Close();
						da.Dispose();
						da = null;
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						if(null != da)
						{
							da.SelectCommand.Connection.Close();
							da.Dispose();
							da = null;
						}
					}

					return null;
				}
				/// <summary>
				/// Gets a DataSet Filled with the results of in passed procedure.
				/// </summary>
				/// <param name="procedure">Name of the nrocedure.</param>
				/// <param name="param">Parameter to be used when execting.</param>
				/// <returns>A DataSet or null.</returns>
				public DataSet ExecuteDataSet(string procedure, System.Data.OracleClient.OracleParameter param)
				{
					if(null == conn) return null;

					DataSet ret = null;
					System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(procedure, param);

					try
					{

						if(da == null)
							return ret;

                        ret = new DataSet();
                        da.Fill(ret);
						da.SelectCommand.Connection.Close();
						da.Dispose();
						da = null;
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						if(null != da)
						{
							da.SelectCommand.Connection.Close();
							da.Dispose();
							da = null;
						}
					}

					return ret;
				}
				/// <summary>
				/// Gets a DataSet Filled with the results of in passed procedure.
				/// </summary>
				/// <param name="procedure">Name of the nrocedure.</param>
				/// <param name="param">Parameters to be used when execting.</param>
				/// <returns>A DataSet or null.</returns>
				public DataSet ExecuteDataSet(string procedure, System.Data.OracleClient.OracleParameter[] param)
				{
					if(null == conn) return null;

					DataSet ret = null;
					System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(procedure, param);

					try
					{

						if(da == null)
							return ret;

                        ret = new DataSet();
                        da.Fill(ret);
						da.SelectCommand.Connection.Close();
						da.Dispose();
						da = null;
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						if(null != da)
						{
							da.SelectCommand.Connection.Close();
							da.Dispose();
							da = null;
						}
					}

					return ret;
				}
				#endregion
				#region ExecuteDataTable
				/// <summary>
				/// Gets a DataTable Filled with the results of in passed procedure.
				/// </summary>
				/// <param name="procedure">Name of the nrocedure.</param>
				/// <returns>A DataTable or null.</returns>
				public DataTable ExecuteDataTable(string procedure)
				{
					if(null == conn) return null;

					DataSet ret = null;
					System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(procedure, CommandType.StoredProcedure);

					try
					{

						if(da == null)
							return null;

                        ret = new DataSet();
                        da.Fill(ret);
					
						da.SelectCommand.Connection.Close();
						da.Dispose();
						da = null;

						if(ret.Tables.Count > 0)
							return ret.Tables[0];
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						if(null != da)
						{
							da.SelectCommand.Connection.Close();
							da.Dispose();
							da = null;
						}
					}

					return null;
				}
				/// <summary>
				/// Gets a DataTable Filled with the results of in passed procedure.
				/// </summary>
				/// <param name="procedure">Name of the nrocedure.</param>
				/// <param name="param">Parameter to be used when execting.</param>
				/// <returns>A DataTable or null.</returns>
				public DataTable ExecuteDataTable(string procedure, System.Data.OracleClient.OracleParameter param)
				{
					if(null == conn) return null;

					DataSet ret = null;
					System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(procedure, param);

                    //Oracle.DataAccess
					try
					{

						if(da == null)
							return null;

                        ret = new DataSet();
                        da.Fill(ret);
						da.SelectCommand.Connection.Close();
						da.Dispose();
						da = null;

						if(ret.Tables.Count > 0)
							return ret.Tables[0];
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						if(null != da)
						{
							da.SelectCommand.Connection.Close();
							da.Dispose();
							da = null;
						}
					}

					return null;
				}
				/// <summary>
				/// Gets a DataTable Filled with the results of in passed procedure.
				/// </summary>
				/// <param name="procedure">Name of the nrocedure.</param>
				/// <param name="param">Parameters to be used when execting.</param>
				/// <returns>A DataTable or null.</returns>
				public DataTable ExecuteDataTable(string procedure, System.Data.OracleClient.OracleParameter[] param)
				{
					if(null == conn) return null;

					DataSet ret = null;
					System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(procedure, param);

					try
					{

						if(da == null)
							return null;

                        ret = new DataSet();
                        da.Fill(ret);

						da.SelectCommand.Connection.Close();
						da.Dispose();
						da = null;

						if(ret.Tables.Count > 0)
							return ret.Tables[0];
					}
					catch(Exception ex){ThorwGenProcErr(procedure, ex);}
					finally
					{
						if(null != da)
						{
							da.SelectCommand.Connection.Close();
							da.Dispose();
							da = null;
						}
					}

					return null;
				}
				#endregion
			}
			#endregion
			#region Nested Class: SqlText
			/// <summary>Provides methods to access and manipulate a ODP.Net DataSource Database through ODP.Net  text.</summary>
			[Serializable]
			public sealed class SqlText
			{
				#region Construction
				OracleDB conn = null;
				public SqlText(OracleDB connection){this.conn = connection;}
				#endregion
				#region Throw Error
				void ThorwGenProcErr(string sql, System.Exception ex){throw new Exception("An error occuried in ODP.Net DataSource when trying to execute the following: \r\n" + sql + ". More detailed information has been stored in this System.Exceptions InnerException.", ex); }
				#endregion
				#region ExecuteReader
				public System.Data.IDataReader ExecuteReader(string sql)
				{
					if(null == conn) return null;
					System.Data.IDataReader ret = null;
					System.Data.OracleClient.OracleCommand cmd = null;

					try
					{
						cmd = conn.CreateCommand(sql, CommandType.Text);

						if(null == cmd)
							return null;

						ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					}
					catch(Exception ex){ThorwGenProcErr(sql, ex);}

					return ret;
				}

                public System.Data.IDataReader ExecuteReader(string sql, System.Data.OracleClient.OracleParameter param)
                {
                    if (null == conn) return null;
                    System.Data.IDataReader ret = null;
                    System.Data.OracleClient.OracleCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text, true);

                        if (null == cmd)
                            return null;

                        cmd.Parameters.Add(param);

                        ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }

                    return ret;
                }

                public System.Data.IDataReader ExecuteReader(string sql, System.Data.OracleClient.OracleParameter[] @params)
                {
                    if (null == conn) return null;
                    System.Data.IDataReader ret = null;
                    System.Data.OracleClient.OracleCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text, true);

                        if (null == cmd)
                            return null;

                        if (null != @params && @params.Length > 0)
                        {
                            foreach (System.Data.OracleClient.OracleParameter p in @params)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }

                        ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }

                    return ret;
                }
                #endregion
				#region ExecuteScalar
				public object ExecuteScalar(string sql)
				{
					if(null == conn) return null;
					object ret = null;
					System.Data.OracleClient.OracleCommand cmd = null;

					try
					{
						cmd = conn.CreateCommand(sql, CommandType.Text);

						if(null == cmd)
							return null;

						ret = cmd.ExecuteScalar();
					}
					catch(Exception ex){ThorwGenProcErr(sql, ex);}
					finally
					{
						cmd.Connection.Close();
						cmd.Dispose();
						cmd = null;
					}

					return ret;
				}
                public object ExecuteScalar(string sql, System.Data.OracleClient.OracleParameter param)
                {
                    if (null == conn) return null;
                    object ret = null;
                    System.Data.OracleClient.OracleCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text, true);

                        if (null == cmd)
                            return null;


                        cmd.Parameters.Add(param);

                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                    return ret;
                }
                public object ExecuteScalar(string sql, System.Data.OracleClient.OracleParameter[] @params)
                {
                    if (null == conn) return null;
                    object ret = null;
                    System.Data.OracleClient.OracleCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text, true);

                        if (null == cmd)
                            return null;


                        if (null != @params && @params.Length > 0)
                        {
                            foreach (System.Data.OracleClient.OracleParameter p in @params)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }

                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                    return ret;
                }
                #endregion
				#region ExecuteNonQuery
				public int ExecuteNonQuery(string sql)
				{
					if(null == conn) return 0;
					System.Data.OracleClient.OracleCommand cmd = null;
                    int ret = 0;
					try
					{
						cmd = conn.CreateCommand(sql, CommandType.Text);

						if(null == cmd)
							return 0;

						ret = cmd.ExecuteNonQuery();
					}
					catch(Exception ex){ThorwGenProcErr(sql, ex);}
					finally
					{
						cmd.Connection.Close();
						cmd.Dispose();
						cmd = null;
					}
                    return ret;
				}
                public int ExecuteNonQuery(string sql, System.Data.OracleClient.OracleParameter param)
                {
                    if (null == conn) return 0;
                    System.Data.OracleClient.OracleCommand cmd = null;
                    int ret = 0;
                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text, true);

                        if (null == cmd)
                            return 0;

                        cmd.Parameters.Add(param);
                        ret = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }
                    return ret;
                }
                public int ExecuteNonQuery(string sql, System.Data.OracleClient.OracleParameter[] @params)
                {
                    if (null == conn) return 0;
                    System.Data.OracleClient.OracleCommand cmd = null;
                    int ret = 0;
                    string tmp = sql;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text, true);

                        if (null == cmd)
                            return 0;

                        if (null != @params && @params.Length > 0)
                        {
                            foreach (System.Data.OracleClient.OracleParameter p in @params)
                            {
                                cmd.Parameters.Add(p);

                                if (p.Value == DBNull.Value)
                                {
                                    tmp = tmp.Replace(":" + p.ParameterName + ",", "null,");
                                }
                                else
                                {
                                    if (p.OracleType == System.Data.OracleClient.OracleType.VarChar)
                                    {
                                        if (Convert.ToString(p.Value).Trim() == "")
                                        {
                                            tmp = tmp.Replace(":" + p.ParameterName+",", "null,");
                                        }
                                        else
                                        {
                                            tmp = tmp.Replace(":" + p.ParameterName + ",", "'" + Convert.ToString(p.Value).Replace("'", "''") + "',");
                                        }
                                    }
                                    else if (p.OracleType == System.Data.OracleClient.OracleType.DateTime)
                                    {
                                        tmp = tmp.Replace(":" + p.ParameterName + ",", "null,");
                                    }
                                    else
                                    {
                                        if (Convert.ToString(p.Value).Trim() == "")
                                        {
                                            tmp = tmp.Replace(":" + p.ParameterName + ",", "null,");
                                        }
                                        else
                                        {
                                            tmp = tmp.Replace(":" + p.ParameterName + ",", Convert.ToString(p.Value) + ",");
                                        }
                                    }
                                }
                            }
                        }

                        ret = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex) 
                    { 
                        ThorwGenProcErr(sql, ex);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }
                    return ret;
                }
                #endregion
				#region ExecuteDataSet
				/// <summary>
				/// Gets a DataSet Filled with the results of in passed query.
				/// </summary>
				/// <param name="sql">Sql query.</param>
				/// <returns>A DataSet or null.</returns>
				public DataSet ExecuteDataSet(string sql)
				{
					if(null == conn) return null;

					DataSet ret = new DataSet();
					System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(sql, CommandType.Text);

					try
					{

						if(da == null)
							return ret;

                        ret = new DataSet();
                        da.Fill(ret);
						da.SelectCommand.Connection.Close();
						da.Dispose();
						da = null;
					}
					catch(Exception ex){ThorwGenProcErr(sql, ex);}
					finally
					{
						if(null != da)
						{
							da.SelectCommand.Connection.Close();
							da.Dispose();
							da = null;
						}
					}

					return null;
				}
                public DataSet ExecuteDataSet(string sql, System.Data.OracleClient.OracleParameter param)
                {
                    if (null == conn) return null;

                    DataSet ret = new DataSet();
                    System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(sql, CommandType.Text, true);



                    try
                    {

                        if (da == null)
                            return ret;
                        da.SelectCommand.Parameters.Add(param);

                        ret = new DataSet();
                        da.Fill(ret);
                        da.SelectCommand.Connection.Close();
                        da.Dispose();
                        da = null;
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }
                    finally
                    {
                        if (null != da)
                        {
                            da.SelectCommand.Connection.Close();
                            da.SelectCommand.Dispose();
                            da.Dispose();
                            da = null;
                        }
                    }

                    return null;
                }
                public DataSet ExecuteDataSet(string sql, System.Data.OracleClient.OracleParameter[] @params)
                {
                    if (null == conn) return null;

                    DataSet ret = new DataSet();
                    System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(sql, CommandType.Text, true);

                    if (null != da && null != @params && @params.Length > 0)
                    {
                        foreach (System.Data.OracleClient.OracleParameter p in @params)
                        {
                            da.SelectCommand.Parameters.Add(p);
                        }
                    }

                    try
                    {

                        if (da == null)
                            return ret;

                        ret = new DataSet();
                        da.Fill(ret);
                        da.SelectCommand.Connection.Close();
                        da.Dispose();
                        da = null;
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }
                    finally
                    {
                        if (null != da)
                        {
                            da.SelectCommand.Connection.Close();
                            da.SelectCommand.Dispose();
                            da.Dispose();
                            da = null;
                        }
                    }

                    return null;
                }
                #endregion
				#region ExecuteDataTable
				/// <summary>
				/// Gets a DataTable Filled with the results of in passed query.
				/// </summary>
				/// <param name="sql">Sql query.</param>
				/// <returns>A DataTable or null.</returns>
				public DataTable ExecuteDataTable(string sql)
				{
					if(null == conn) return null;

					DataSet ret = new DataSet();
					System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(sql, CommandType.Text);
                    
					try
					{

						if(da == null)
							return null;

                        ret = new DataSet();
                        da.Fill(ret);
					
						da.SelectCommand.Connection.Close();
						da.Dispose();
						da = null;

						if(ret.Tables.Count > 0)
							return ret.Tables[0];
					}
					catch(Exception ex){ThorwGenProcErr(sql, ex);}
					finally
					{
						if(null != da)
						{
							da.SelectCommand.Connection.Close();
							da.Dispose();
							da = null;
						}
					}

					return null;
				}
                public DataTable ExecuteDataTable(string sql, System.Data.OracleClient.OracleParameter param)
                {
                    if (null == conn) return null;

                    DataSet ret = new DataSet();
                    System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(sql, CommandType.Text, true);

                    try
                    {

                        if (da == null)
                            return null;

                        da.SelectCommand.Parameters.Add(param);

                        ret = new DataSet();
                        da.Fill(ret);

                        da.SelectCommand.Connection.Close();
                        da.Dispose();
                        da = null;

                        if (ret.Tables.Count > 0)
                            return ret.Tables[0];
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }
                    finally
                    {
                        if (null != da)
                        {
                            da.SelectCommand.Connection.Close();
                            da.Dispose();
                            da = null;
                        }
                    }

                    return null;
                }
                public DataTable ExecuteDataTable(string sql, System.Data.OracleClient.OracleParameter[] @params)
                {
                    if (null == conn) return null;

                    DataSet ret = new DataSet();
                    System.Data.OracleClient.OracleDataAdapter da = conn.CreateDataAdapter(sql, CommandType.Text, true);
                                        
                    if (null != da && null != @params && @params.Length > 0)
                    {
                        foreach (System.Data.OracleClient.OracleParameter p in @params)
                        {
                            da.SelectCommand.Parameters.Add(p);
                        }
                    }

                    try
                    {

                        if (da == null)
                            return null;

                        ret = new DataSet();
                        da.Fill(ret);

                        da.SelectCommand.Connection.Close();
                        da.Dispose();
                        da = null;

                        if (ret.Tables.Count > 0)
                            return ret.Tables[0];
                    }
                    catch (Exception ex) { ThorwGenProcErr(sql, ex); }
                    finally
                    {
                        if (null != da)
                        {
                            da.SelectCommand.Connection.Close();
                            da.Dispose();
                            da = null;
                        }
                    }

                    return null;
                }
                #endregion
			}
			#endregion
			#region Construction
			OracleDB conn = null;
			public OracleDbAccessor(OracleDB connection)
			{
				conn = connection;
				proc = new SqlProcedure(conn);
				sqlTxt = new SqlText(conn);
			}
			#endregion
			#region Helper Properties
			SqlProcedure proc = null;
			public SqlProcedure ExecuteProcedure{get{return proc;}}
            SqlText sqlTxt = null;
            public SqlText ExecuteSqlText { get { return sqlTxt; } }
			#endregion
		}
		#endregion
		#region Construction
		#region CreateFromString
		/// <summary>
		/// Attempts to create a ConnectionSource by parsing an in passed connection string.
		/// </summary>
		/// <param name="conn_str">Connection string.</param>
		/// <returns>A ConnectionSource or null.</returns>
		public static OracleDB CreateFromString(string conn_str)
		{
			OracleDB ret = new OracleDB();

			string[] split01 = conn_str.Split(";".ToCharArray());
			foreach(string s in split01)
			{
				string[] split02 = s.Split("=".ToCharArray());
				try
				{
					switch(split02[0].ToLower())
					{
						case "data source":
						case "dsn":
							ret.dsn = split02[1];
							break;
						case "user id":
						case "uid":
							ret.user = split02[1];
							break;
						case "password":
						case "pwd":
							ret.password = split02[1];
							break;
						default:
							if(null != s && s != ";" && s != string.Empty) 
								ret.ex += s + ";";//split02[0] + "=" + split02[1] + ";";
							break;
					}
				}
				catch(Exception ex)
				{
					System.Diagnostics.Debug.WriteLine(ex.Message);
                    
				}
			}
			ret.is_dirty = true;
			string ret_s = ret.ConnectionString;
			return ret;
		}
		#endregion
		public OracleDB()
		{
			is_dirty = true;
			db_hit = new OracleDbAccessor(this);
		}
		internal OracleDB(string dsn):this(){this.dsn = dsn;}
		public OracleDB(string dsn, string user, string password):this(dsn)
		{
			this.user = user;
			this.password = password;
		}
		#endregion
		#region Properties
		#region DataSource
		string dsn = string.Empty;
        public string DataSource
		{
			get{return this.dsn;}
			set
			{
				if(value != this.dsn)
				{
					this.dsn = value;
					this.is_dirty = true;
				}
			}
		}
		#endregion
		#region User
		string user = string.Empty;
		public string User
		{
			get{return this.user;}
			set
			{
				if(value != this.user)
				{
					this.user = value;
					this.is_dirty = true;
				}
			}
		}
		#endregion
		#region Password
		string password = string.Empty;
		public string Password
		{
			get{return this.password;}
			set
			{
				if(value != this.password)
				{
					this.password = value;
					this.is_dirty = true;
				}
			}
		}
		#endregion
		#region ConnectionStringEx
		string ex = string.Empty;
		public string Extended
		{
			get{return this.ex;}
			set
			{
				if(value != this.ex)
				{
					this.ex = value;
					this.is_dirty = true;
				}
			}
		}
		#endregion
		#region ConnectionString
		bool is_dirty = false;
		string connection_string = string.Empty;
		public string ConnectionString
		{
			get
			{
				if(is_dirty || null == connection_string || connection_string == string.Empty)
				{
					StringBuilder sb = new StringBuilder();

                    //sb.Append("PROVIDER=" + provider + ";");

					if(this.dsn != string.Empty)
						sb.Append("DATA SOURCE=" + this.dsn + ";");
					
					if(this.user != string.Empty && this.password != string.Empty)
					{
						sb.Append("User ID=" + this.User + ";");
						sb.Append("Password=" + this.password + ";");
					}

					if(this.ex != string.Empty)
						sb.Append(ex);

					connection_string = sb.ToString();

					sb = null;
					this.is_dirty = false;
				}

				if(connection_string != string.Empty && user == string.Empty)
				{
					//string c1 = connection_string.ToUpper();
					//string c2 = "Integrated Security=SSPI;Persist Security Info=False;".ToUpper();
					//if(c1.IndexOf(c2) == -1)
					//{
					//	if(connection_string.LastIndexOf(";") != (connection_string.Length - 1))
					//		connection_string += ";";

					//	connection_string += "Integrated Security=SSPI;Persist Security Info=False;";
					//}

                    throw new Exception("Connection String Invalid.");
				}

				return connection_string;
			}
		}
		#endregion
		#region IsValid
		/// <summary>
		/// Attempts to open a connection using this connection string. 
		/// Returns true after closing the connection if a connection was opened 
		/// otherwise false is returned.
		/// </summary>
		public bool IsValid
		{
			get
			{
				bool is_valid = false;
				System.Data.OracleClient.OracleConnection connDb = new System.Data.OracleClient.OracleConnection(ConnectionString);
				try
				{

					connDb.Open();
					connDb.Close();
					is_valid = true;
				}
				catch(Exception ex)
				{
					System.Diagnostics.Debug.WriteLine(ex.Message);
					is_valid = false;
				}
				finally
				{
					connDb.Dispose();
					connDb = null;
				}
				return is_valid;
			}
		}
		#endregion
		#region Accessor
		OracleDbAccessor db_hit;
		/// <summary>
		/// Provides properties and methods to be used when accesing the database.
		/// </summary>
		public OracleDbAccessor DbAccessor{get{return db_hit;}}
		#endregion
		#endregion
		#region Methods
		#region BeginTransaction
		/// <summary>
		/// Returns a System.Data.OracleClient.OracleTransaction ready for use.
		/// </summary>
		/// <param name="iso">Transaction IsolationLevel.</param>
		/// <returns></returns>
		public System.Data.OracleClient.OracleTransaction BeginTransaction(IsolationLevel iso)
		{
			return CreateConnection().BeginTransaction(iso);
		}
		#endregion
		#region CreateConnection
		/// <summary>
		/// Get an open instance of an System.Data.OracleClient.OracleConnection pointed at this ConnectionString.
		/// </summary>
		/// <returns>An Open System.Data.OracleClient.OracleConnection or null.</returns>
		public System.Data.OracleClient.OracleConnection CreateConnection()
		{
			System.Data.OracleClient.OracleConnection connDb = null;
			try
			{	//don't waste ticks on an IsValid check
				connDb = new System.Data.OracleClient.OracleConnection(ConnectionString);
				connDb.Open();
			}
			catch(Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
				connDb = null;
				throw ex;
			}
			return connDb;
		}
		#endregion
		#region CreateCommand
		/// <summary>
		/// Gets a System.Data.OracleClient.OracleCommand object initialized to this connection.
		/// </summary>
		/// <returns>A System.Data.OracleClient.OracleCommand or null.</returns>
		public System.Data.OracleClient.OracleCommand CreateCommand()
		{
			System.Data.OracleClient.OracleCommand cmd = null;

			try
			{
				System.Data.OracleClient.OracleConnection conn = CreateConnection();

				if(null == conn)
					return cmd;

				cmd = new System.Data.OracleClient.OracleCommand(string.Empty, conn);
			}
			catch(Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
				cmd = null;
			}

			return cmd;
		}
		/// <summary>
		/// Gets a System.Data.OracleClient.OracleCommand object initialized to this connection and set to the in passed command.
		/// </summary>
		/// <param name="command_text">The sql, table name or stored procedure name to use.</param>
		/// <param name="type">The type of command text passed in.</param>
		/// <returns>A System.Data.OracleClient.OracleCommand or null.</returns>
		public System.Data.OracleClient.OracleCommand CreateCommand(string command_text, CommandType type)
		{
			System.Data.OracleClient.OracleConnection conn = CreateConnection();
			System.Data.OracleClient.OracleCommand cmd = null;

			if(null == conn)
				return cmd;

			cmd = new System.Data.OracleClient.OracleCommand(command_text, conn);
			cmd.CommandType = type;

			return cmd;
		}

        public System.Data.OracleClient.OracleCommand CreateCommand(string command_text, CommandType type, bool bindByName)
        {
            System.Data.OracleClient.OracleConnection conn = CreateConnection();
            System.Data.OracleClient.OracleCommand cmd = null;

            if (null == conn)
                return cmd;

            cmd = new System.Data.OracleClient.OracleCommand(command_text, conn);

            cmd.CommandType = type;
            
            return cmd;
        }

		/// <summary>
		/// Gets a System.Data.OracleClient.OracleCommand initialized to this connection and set to use the in passed procedure.
		/// </summary>
		/// <param name="procedure_name">Stored procedure name.</param>
		/// <param name="param">Parameter to be used by the procedure.</param>
		/// <returns>A System.Data.OracleClient.OracleCommand or null.</returns>
		public System.Data.OracleClient.OracleCommand CreateCommand(string procedure_name, System.Data.OracleClient.OracleParameter param)
		{
			System.Data.OracleClient.OracleCommand cmd = CreateCommand(procedure_name, CommandType.StoredProcedure);
			
			if(null == cmd)
				return cmd;

			if(null !=param)
				cmd.Parameters.Add(param);

			return cmd;
		}

		/// <summary>
		/// Gets a System.Data.OracleClient.OracleCommand initialized to this connection and set to use the in passed procedure.
		/// </summary>
		/// <param name="procedure_name">Stored procedure name.</param>
		/// <param name="param">Parameters to be used by the procedure.</param>
		/// <returns>A System.Data.OracleClient.OracleCommand or null.</returns>
		public System.Data.OracleClient.OracleCommand CreateCommand(string procedure_name, System.Data.OracleClient.OracleParameter[] param)
		{
			System.Data.OracleClient.OracleCommand cmd = CreateCommand(procedure_name, CommandType.StoredProcedure);
			
			if(null == cmd)
				return cmd;

			if(param.Length > 0)
			{
				for(int i = 0; i < param.Length; i ++)
					cmd.Parameters.Add(param[i]);
			}

			return cmd;
		}
		#endregion
		#region CreateDataAdapter
		/// <summary>
		/// Creates a ORacleDatAdapter initalized to this connection and set to use the in passed info.
		/// </summary>
		/// <param name="command_text">The sql, Table or Stored Procedure to be used.</param>
		/// <param name="type">The type of command execution.</param>
		/// <returns>A System.Data.OracleClient.OracleDataAdapter or null.</returns>
		public System.Data.OracleClient.OracleDataAdapter CreateDataAdapter(string command_text, CommandType type)
		{
			System.Data.OracleClient.OracleDataAdapter ret = null;

			try
			{
				System.Data.OracleClient.OracleCommand cmd = CreateCommand(command_text, type);

				if(null == cmd)
					return ret;
                
				ret = new System.Data.OracleClient.OracleDataAdapter(cmd);
			}
			catch(Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
				ret = null;
			}
			return ret;
		}
        public System.Data.OracleClient.OracleDataAdapter CreateDataAdapter(string command_text, CommandType type, bool bindByName)
        {
            System.Data.OracleClient.OracleDataAdapter ret = null;

            try
            {
                System.Data.OracleClient.OracleCommand cmd = CreateCommand(command_text, type, bindByName);

                if (null == cmd)
                    return ret;

                ret = new System.Data.OracleClient.OracleDataAdapter(cmd);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
                ret = null;
            }
            return ret;
        }
        /// <summary>
		/// Creates a OracleDatAdapter initalized to this connection and set to use the in passed info.
		/// </summary>
		/// <param name="procedure_name">Stored procedure name.</param>
		/// <param name="param">Parameter to be used by the procedure.</param>
		/// <returns>A System.Data.OracleClient.OracleDataAdapter or null.</returns>
		public System.Data.OracleClient.OracleDataAdapter CreateDataAdapter(string procedure, System.Data.OracleClient.OracleParameter param)
		{
			System.Data.OracleClient.OracleDataAdapter ret = null;

			try
			{
				System.Data.OracleClient.OracleCommand cmd = CreateCommand(procedure, param);

				if(null == cmd)
					return ret;

				ret = new System.Data.OracleClient.OracleDataAdapter(cmd);
			}
			catch(Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
				ret = null;
			}
			return ret;
		}
		/// <summary>
		/// Creates a OracleDatAdapter initalized to this connection and set to use the in passed info.
		/// </summary>
		/// <param name="procedure_name">Stored procedure name.</param>
		/// <param name="param">Parameters to be used by the procedure.</param>
		/// <returns>A System.Data.OracleClient.OracleDataAdapter or null.</returns>
		public System.Data.OracleClient.OracleDataAdapter CreateDataAdapter(string procedure, System.Data.OracleClient.OracleParameter[] param)
		{
			System.Data.OracleClient.OracleDataAdapter ret = null;

			try
			{
				System.Data.OracleClient.OracleCommand cmd = CreateCommand(procedure, param);

				if(null == cmd)
					return ret;

				ret = new System.Data.OracleClient.OracleDataAdapter(cmd);
			}
			catch(Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
				ret = null;
			}
			return ret;
		}
		#endregion
        #region CreateParameter

        public System.Data.DbType GetDbType(object value)
        {
            System.Data.DbType type = System.Data.DbType.String;
            string typeName = value.GetType().FullName;

            switch (typeName)
            {
                case "System.Int16":
                    type = System.Data.DbType.Int16;
                    break;
                case "System.Int32":
                    type = System.Data.DbType.Int32;
                    break;
                case "System.Int64":
                    type = System.Data.DbType.Int64;
                    break;
                case "System.Decimal":
                    type = System.Data.DbType.Decimal;
                    break;
                case "System.Double":
                    type = System.Data.DbType.Double;
                    break;
                case "System.Single":
                    type = System.Data.DbType.Single;
                    break;
                case "System.String":
                    type = System.Data.DbType.String;
                    break;
                case "System.DateTime":
                    type = System.Data.DbType.Date;
                    break;
            }

            return type;
        }

        public System.Data.OracleClient.OracleType GetOracleType(object value)
        {
            System.Data.OracleClient.OracleType type = System.Data.OracleClient.OracleType.VarChar;
            string typeName = value.GetType().FullName;

            switch (typeName)
            {
                case "System.Byte[]":
                    return System.Data.OracleClient.OracleType.Blob;
                case "System.Int16":
                    type = System.Data.OracleClient.OracleType.Int16;
                    break;
                case "System.Int32":
                    type = System.Data.OracleClient.OracleType.Int32;
                    break;
                case "System.Int64":
                    type = System.Data.OracleClient.OracleType.Number;
                    break;
                case "System.Decimal":
                    type = System.Data.OracleClient.OracleType.Number;
                    break;
                case "System.Double":
                    type = System.Data.OracleClient.OracleType.Double;
                    break;
                case "System.Single":
                    type = System.Data.OracleClient.OracleType.Number;
                    break;
                case "System.String":
                    type = System.Data.OracleClient.OracleType.VarChar;
                    break;
                case "System.DateTime":
                    type = System.Data.OracleClient.OracleType.DateTime;
                    break;
            }

            return type;
        }

        public System.Data.OracleClient.OracleParameter CreateParameter(string name, object value, System.Data.ParameterDirection direction,System.Data.OracleClient.OracleType type)
        {
            if (null == name || name.Trim().Length == 0) return null;
            if (null == value) return null;

            System.Data.OracleClient.OracleParameter ret = new System.Data.OracleClient.OracleParameter();

            ret.ParameterName = name;
            ret.Direction = direction;
            ret.OracleType = type;
            ret.Value = value;

            return ret;
        }

        public System.Data.OracleClient.OracleParameter CreateParameter(string name, object value)
        {
            if (null == name || name.Trim().Length == 0) return null;
            if (null == value) return null;

            System.Data.OracleClient.OracleType type = GetOracleType(value);
            return CreateParameter(name, value, ParameterDirection.Input, type);
        }

        #endregion
        #region CleanConnection
        /// <summary>
		/// Close, dispose, and set an System.Data.OracleClient.OracleConnection to null.
		/// </summary>
		/// <param name="conn"></param>
		public void CleanConnection(System.Data.OracleClient.OracleConnection conn)
		{
			if(conn != null)
			{
				conn.Close();
				conn.Dispose();
				conn = null;
			}
		}
		#endregion
		#region CleanCommand
		/// <summary>
		/// Dispose of a System.Data.OracleClient.OracleCommand and set it equal to null.
		/// </summary>
		/// <param name="cmd">The System.Data.OracleClient.OracleCommand.</param>
		public void CleanCommand(System.Data.OracleClient.OracleCommand cmd)
		{
			if(cmd != null)
			{
                if (null != cmd.Connection)
                {   //not sure this is needed but better safe than sorry.
                    CleanConnection(cmd.Connection);
                }

				cmd.Dispose();
				cmd = null;
			}
		}
		#endregion
		#region CleanDataReader
		/// <summary>
		/// Close a System.Data.IDataReader and set it equal to null. 
		/// If the reader was created with CommandBehavior.CloseConnection the 
		/// connection associated with the reader will be closed.
		/// </summary>
		/// <param name="r">The System.Data.IDataReader.</param>
		public void CleanDataReader(System.Data.IDataReader r)
		{
			if(r != null)
			{
				r.Close();
				r = null;
			}
		}
		#endregion
		#region CleanDataAdapter
		/// <summary>
		/// Dispose of a System.Data.OracleClient.OracleDataAdapter and set it to null.
		/// </summary>
		/// <param name="adapt">The System.Data.OracleClient.OracleDataAdapter to be cleaned.</param>
		public void CleanDataAdapter(System.Data.OracleClient.OracleDataAdapter adapt)
		{
			if(adapt != null)
			{
				adapt.Dispose();
				adapt = null;
			}
		}
		#endregion
		#region CleanTransaction
		/// <summary>
		/// Dispose of a System.Data.OracleClient.OracleTransaction nd set it qua to null.
		/// </summary>
		/// <param name="trans"></param>
		public void CleanTransaction(System.Data.OracleClient.OracleTransaction trans)
		{
			if(trans != null)
			{
				trans.Dispose();
				trans = null;
			}
		}
		#endregion
		#region Clone
		/// <summary>
		/// returns an object with an equal value but a diffrent address space a.k.a memory allocation a.k.a. non-equal refreance.
		/// </summary>
		/// <returns></returns>
		public object Clone()
		{
			OracleDB ret = new OracleDB(dsn, user, password);
            ret.ex = ex;
			return ret;
		}
		#endregion
		#region CloneExplicit
		/// <summary>
		/// Returns a clone of the object already cast for use.
		/// </summary>
		/// <returns>A OracleDB.</returns>
		public OracleDB CloneExplicit()
		{
			return (OracleDB)Clone();
		}
		#endregion
		#endregion
	}
	#endregion
}